import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PopulationQuery {
	private static final String InsertQueryUsers = "LOAD DATA INFILE ? "
			+ "INTO TABLE users " + "FIELDS TERMINATED BY ';' "
			+ "ENCLOSED BY '\"' " + "LINES TERMINATED BY '\\n';";
	private static final String InsertQueryUsersHasGenres = "LOAD DATA INFILE ? "
			+ "INTO TABLE users_has_genres "
			+ "FIELDS TERMINATED BY ';' "
			+ "ENCLOSED BY '\"' " + "LINES TERMINATED BY '\\n';";
	private static final String ClearUsersTableQuery = "DELETE FROM Users";
	// private static String MOVIES_TABLE_FILE= "movies.csv";
	// private static String MOVIES_HAS_ACTORS_TABLE_FILE=
	// "movies_has_actors.csv";
	// private static String MOVIES_HAS_GENRES_TABLE_FILE=
	// "movies_has_genres.csv";
	// private static String GENRES_TABLE_FILE= "genres.csv";
	private static String USERS_TABLE_FILE = "users.csv";
	private static String USERS_HAS_GENRES_TABLE_FILE = "users_has_genres.csv";

	/**
	 * Popola le tabelle users e users_has_generes
	 * 
	 * @param connection
	 */
	public static void populateUsersTable(MySQLConnection connection) {

		try {
			PreparedStatement statement;
			File file = new File(USERS_TABLE_FILE);
			String filePath = file.getAbsolutePath();
			// Inserimento in users

			statement = connection.createPreparedStatement(InsertQueryUsers);
			statement.setString(1, filePath);

			file = new File(USERS_HAS_GENRES_TABLE_FILE);
			filePath = file.getAbsolutePath();
			// Inserimento in users_has_genres

			statement = connection
					.createPreparedStatement(InsertQueryUsersHasGenres);
			statement.setString(1, filePath);
			statement.executeUpdate();
			statement.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * Svuota le tabelle users e users_has_genres
	 * 
	 * @param connection
	 */
	public static void clearUsersTable(MySQLConnection connection) {

		try {

			connection.executeUpdate(ClearUsersTableQuery);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static final String InsertInMovies = "INSERT INTO movies(title, release_date, thumbnail_url, playbill_url, trailer_url, directors_name, directors_surname) VALUES(?,?,?,?,?,?,?)";
	public static final String InsertInMoviesHasActors = "INSERT INTO movies_has_actors(movies_id, actors_name, actors_surname) VALUES(?,?,?)";
	public static final String InsertInMoviesHasGenres = "INSERT INTO movies_has_genres(movies_id, genres_id) VALUES(?,?)";
	public static final String SelectLast = "SELECT id FROM miviamovieDB.movies ORDER BY id DESC LIMIT 1";
	public static final String DeleteMovies = "DELETE FROM movies";

	public static void insertDetailedMovie(MySQLConnection conn,
			DBMovie movie) {
		PreparedStatement statement;
		try {
			statement = conn.createPreparedStatement(InsertInMovies);
			statement.setString(1, movie.getTitle());
			statement.setDate(2, movie.getDate());
			statement.setString(3, movie.getThumbnailURL());
			statement.setString(4, movie.getPlaybillURL());
			statement.setString(5, movie.getTrailerURL());
			statement.setString(6, movie.getDirector().getName());
			statement.setString(7, movie.getDirector().getSurname());
			statement.executeUpdate();
			statement.close();

			statement = conn.createPreparedStatement(SelectLast);
			ResultSet rs = statement.executeQuery();
			int last = 0;
			if (rs.next())
				last = rs.getInt(1);
			rs.close();
			statement.close();

			for (Person p : movie.getActors()) {
				statement = conn
						.createPreparedStatement(InsertInMoviesHasActors);
				statement.setInt(1, last);
				statement.setString(2, p.getName());
				statement.setString(3, p.getSurname());
				statement.executeUpdate();
				statement.close();

			}

			for (MovieType mt : movie.getMovieTypes()) {
				statement = conn
						.createPreparedStatement(InsertInMoviesHasGenres);
				statement.setInt(1, last);
				statement.setString(2, mt.getKey());
				statement.executeUpdate();
				statement.close();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void deleteMovies(MySQLConnection connection) {

		try {
			connection.executeUpdate(DeleteMovies);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}
